<#assign object = doc.object>
package ${object.package}.service.base;

import java.util.HashMap;
import java.util.Map;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.io.PrintWriter;

import redora.rdo.impl.ServiceBase;
import ${object.package}.model.fields.${object.name}Fields;
import ${object.package}.service.${object.name}SQL;
import redora.rdo.exceptions.ConnectException;
import redora.rdo.exceptions.QueryException;
import redora.rdo.exceptions.PersistException;

/**
* Handler for the model vs database synchronization. It can create the ${object.name} table if it does exists
* and it can check if the database definition is still in sync with your model with the check() function.
* For your convenience is can also drop the ${object.name} table.
*/
public class ${object.name}TableUpdate extends ServiceBase {
	private Logger l = Logger.getLogger("${object.package}.service.base.${object.name}TableUpdate");
	
	/**
	* Initializes ${object.name}TableUpdate and connects to the database.
	* The connection will be cached until you issue close(). 
	* @throws ConnectException When a connection cannot be established.
	*/
	public ${object.name}TableUpdate() throws ConnectException {
		super();
	}

  	/** 
  	* Creates the database table if it does not exit. If ${object.name} has
  	* n-to-m relationships, the relationship tables will also be created.
  	*/
  	public void create() throws ConnectException, QueryException {
  		connect();
  		String SQL = null;
    	try {
      		if(!tableExists("${object.name}")) {
	        	l.log(Level.FINE, "Table ${object.name} does not exist, i will create it now."); 
	        	s = con.createStatement();
	        	SQL = ${object.name}SQL.CREATE_TABLE;
	        	s.execute(SQL);
	        	s.close();
      		}
	<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
	<@relation_table att=att />
      		if(!tableExists("${relation_table_name}")) {
        		l.log(Level.FINE, "Table ${relation_table_name} does not exist, i will create it now."); 
        		s = con.createStatement();
        		SQL = ${object.name}SQL.CREATE_${relation_table_name?upper_case}_TABLE;
        		s.execute(SQL);
        		s.close();
      		}
	</#list>
    	} catch (SQLException e) {
      		l.log(Level.SEVERE, "Failed to create table using this statement: " + SQL, e);
      		throw new QueryException("Failed to create table using this statement: " + SQL, e);
    	}
  	}

  	/** 
  	* Removes the table from the database. This of course cannot be undone!
  	* If ${object.name} has n-to-m relationships, their corresponding relation tables
  	* will be removed.
  	*/
  	public void drop() throws ConnectException, PersistException {
    	connect();
    	String SQL = null;
    	try {
      		if (tableExists("${object.name}")) {
        		s = con.createStatement();
        		SQL = "drop table ${object.name}";
        		s.execute(SQL);
        		s.close();
      		}
	<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
	<@relation_table att=att />
      		if (tableExists("${relation_table_name}")) {
        		l.log(Level.FINE, "I will drop ${relation_table_name} now."); 
        		s = con.createStatement();
        		SQL = "drop table ${relation_table_name}";
        		s.execute(SQL);
        		s.close();
      		}
	</#list>
    	} catch (SQLException e) {
      		l.log(Level.SEVERE, "Failed to drop table using this statement: " + SQL, e);
      		throw new PersistException("Failed to drop table", e);
    	} catch (QueryException e) {
      		l.log(Level.SEVERE, "Failed to check if table was dropped", e);
      		throw new PersistException("Failed to check if table was dropped", e);
    	}
  	}
  
	/** 
	* Checks if the model is the same with the database table, logs warning level message
	* if this is not the case.
	* @return True if the model and the database table are the same
	*/
	public synchronized boolean check(PrintWriter out) throws ConnectException, QueryException {
		boolean retVal = true;
		try {
			connect();
			rs = con.getMetaData().getColumns("", "", "${object.name}", "");
			Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
			while (rs.next()) {
				Map<String, Object> list = new HashMap<String, Object>();
				list.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
				list.put("DATA_TYPE", rs.getInt("DATA_TYPE"));
				list.put("COLUMN_SIZE", rs.getInt("COLUMN_SIZE"));
				list.put("IS_NULLABLE", rs.getString("IS_NULLABLE"));
				map.put(rs.getString("COLUMN_NAME"), list);
			}
			for (String key : map.keySet()) {
				boolean check = false;
				for (${object.name?cap_first}Fields fields : ${object.name?cap_first}Fields.values()) {
					if (map.get(key).get("COLUMN_NAME").toString().equalsIgnoreCase(fields.name())) {
						check = true;
						break;
					}
					<#if object.trashcan[0]?? && object.trashcan == "true">
					if (map.get(key).get("COLUMN_NAME").toString().equalsIgnoreCase("roDeleted")) {
						check = true;
						break;
					}
					</#if>
				}
				if (!check) {
					l.log(Level.WARNING, "${object.name} does not have " + key
							+ " attribute but it exists in database");
					if (out != null)
						out.println("${object.name} does not have " + key
								+ " attribute but it exists in database." + "<br>");
					retVal = false;
				}
			}
			for (${object.name?cap_first}Fields fields : ${object.name?cap_first}Fields.values()) {
				if (fields.name().equalsIgnoreCase("creationDate"))
					fields.sqlType = java.sql.Types.TIMESTAMP;
				if (fields.name().equalsIgnoreCase("updateDate"))
					fields.sqlType = java.sql.Types.TIMESTAMP;	
				<#list object.attributes?children as att>
					<#if att?node_name == "datetime">
				if (fields.name().equalsIgnoreCase("${att.@name}"))
					fields.sqlType = java.sql.Types.TIMESTAMP;
					</#if>
				</#list>
				if (fields.sqlType == java.sql.Types.BOOLEAN)
					fields.sqlType = java.sql.Types.BIT;
				if (map.containsKey(fields.name()) && Integer.parseInt(map.get(fields.name()).get("DATA_TYPE").toString()) == fields.sqlType) {
					<#list object.attributes?children as att>
					<#if att?node_name != "set" && att?node_type == "element">
					if(fields.name().equalsIgnoreCase("${att.@name[0]!att.@class}")){
						<#if att.@notnull[0]?? && att.@notnull[0] == "true">
						if(!map.get(fields.name()).get("IS_NULLABLE").toString().equalsIgnoreCase("NO")){
							l.log(Level.WARNING, "${object.name}." + fields.name()+ " can not have null values");
							if(out != null)
								out.println("${object.name}." + fields.name()+ " can't have null values." + "<br>");
							retVal = false;
						}
						<#else>
						if(!map.get(fields.name()).get("IS_NULLABLE").toString().equalsIgnoreCase("YES")){
							l.log(Level.WARNING,"${object.name}." + fields.name()+ " can have null values");
							if(out != null)
								out.println("${object.name}." + fields.name()+ " can't have null values." + "<br>");
							retVal = false;
						}
						</#if>
						<#if att?node_name=="string" && att.@maxlength[0]??>
						if(Integer.parseInt(map.get(fields.name()).get("COLUMN_SIZE").toString()) != <#if att.@maxlength[0]?number &gt; 65000><#if att.@maxlength[0]?number &gt; 16000000>2147483647<#else>16777215</#if><#else>${att.@maxlength[0]}</#if>){
							l.log(Level.WARNING,"${object.name}." + fields.name()+ "'s maxlength do not match the database");
							if(out != null)
								out.println("${object.name}." + fields.name()+ "'s maxlength do not match the database." + "<br>");
							retVal = false;
						}
						</#if>
						<#if att?node_name=="enum">
							<#assign enumValue = "enum(">
							<#list att.element as value>
								<#assign enumValue = enumValue + "'" + "${value.@name}" + "'">
								<#if value_has_next>
									<#assign enumValue = enumValue + "," >
								</#if>
							</#list>
							<#assign enumValue = enumValue + ")">
						String enumValue = "";
						s = con.createStatement();
						rs = s.executeQuery("show columns from ${object.name} like '${att.@class?uncap_first}'");
						while (rs.next()){
							enumValue = rs.getString("Type");
						}
						if (!enumValue.equals("${enumValue}")){
							l.log(Level.WARNING,"${object.name}." + fields.name()+ "'s value ${enumValue} do not match the database's value " + enumValue);
							if(out != null)
								out.println("${object.name}." + fields.name()+ "'s value ${enumValue} do not match the database's value " + enumValue + ".<br>");
							retVal = false;
						}
						</#if>
					}
					</#if>
					</#list>
				} else {
					l.log(Level.WARNING,"${object.name}." + fields.name() + " does not existed or its sqltype does not match according to xml");
					if(out != null)
						out.println("${object.name}." + fields.name() + " does not existed or its sqltype does not match according to xml." + "<br>");
					retVal = false;
				}
			}
		} catch (SQLException e) {
			l.log(Level.SEVERE, "Failed to execute checkField ${object.name}", e);
			throw new QueryException("failed to execute checkField "
					+ "${object.name?cap_first}", e);
		}
		
		return retVal;
	}
}
